const Router = require("@koa/router");
const mysql = require("mysql2/promise");
const router = new Router({ prefix: "/hauty" });
const path = require('path');
require('dotenv').config({ path: path.resolve(__dirname, '../.env') });

// 配置 MySQL 连接池
const pool = mysql.createPool({
  user: process.env.DB_USER2,
  host: process.env.DB_HOST2,
  database: process.env.DB_DATABASE2,
  password: process.env.DB_PASSWORD2,
  port: process.env.DB_PORT2,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 获取报价列表
router.get("/", async (ctx) => {
  try {
    const [rows] = await pool.query("SELECT * FROM `baojia`");
    ctx.body = { success: true, data: rows };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 获取报价列表失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 获取灯具列表（分页、搜索、筛选）
router.post('/lamp-list', async (ctx) => {
  try {
    const { page = 1, pageSize = 10, keyword, inch, type, mt, orderBy = 'createTime' } = ctx.request.body;
    const offset = (page - 1) * pageSize;
    
    // 构建 WHERE 条件
    const whereConditions = [];
    const queryParams = [];
    
    if (inch) {
      whereConditions.push('inch = ?');
      queryParams.push(inch);
    }
    
    if (type) {
      whereConditions.push('type = ?');
      queryParams.push(type);
    }
    
    if (mt) {
      whereConditions.push('mt = ?');
      queryParams.push(mt);
    }
    
    if (keyword) {
      whereConditions.push('(name LIKE ? OR id LIKE ? OR remark LIKE ?)');
      const keywordPattern = `%${keyword}%`;
      queryParams.push(keywordPattern, keywordPattern, keywordPattern);
    }
    
    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';
    
    // 验证 orderBy 字段，防止 SQL 注入
    const allowedOrderFields = ['createTime', 'id', 'name', 'inch', 'type', 'mt'];
    const safeOrderBy = allowedOrderFields.includes(orderBy) ? orderBy : 'createTime';
    
    // 查询总数
    const countQuery = `SELECT COUNT(*) as total FROM hautylamp ${whereClause}`;
    const [countResult] = await pool.query(countQuery, queryParams);
    const total = countResult[0].total;
    
    // 查询数据
    const dataQuery = `
      SELECT * FROM hautylamp 
      ${whereClause}
      ORDER BY ${safeOrderBy} ASC
      LIMIT ? OFFSET ?
    `;
    const dataParams = [...queryParams, pageSize, offset];
    const [items] = await pool.query(dataQuery, dataParams);
    
    ctx.body = {
      success: true,
      data: {
        total,
        items,
        page,
        pageSize
      }
    };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 获取灯具列表失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

module.exports = router;